#!/usr/bin/env python3
# generate_dashboard_static.py
import pandas as pd
import argparse
from datetime import datetime

# 当前周
now = datetime.now()
year, week_num = now.year, now.isocalendar()[1] - 1

# week_num = now.isocalendar()[1] - 1

def generate_dashboard(input_file, output_file=None):
    df = pd.read_excel(input_file)

    # 基础指标
    total_tickets  = len(df)
    resolved_tickets = len(df[df['工单状态'].isin(['Resolved', 'Closed'])])
    open_tickets  = len(df[df['工单状态'] == 'Open'])
    total_hours   = round(df['工单耗时'].sum(), 2)

    # 人员工作量
    staff_stats = (df.groupby('运维人员')
                     .agg(工单数量=('工单单号', 'count'),
                          总工时=('工单耗时', 'sum'))
                     .reset_index()
                     .sort_values('工单数量', ascending=False))

    # 项目 TOP3
    project_stats = df['项目名称'].value_counts().head(3).reset_index()
    project_stats.columns = ['项目名称', '工单数量']

    # 任务类型
    task_type_stats = df['任务类型'].value_counts().reset_index()
    task_type_stats.columns = ['任务类型', '数量']

    # 未完成、已完成明细
    open_df   = df[df['工单状态'] == 'Open'][['项目名称', '工单主题', '工单单号', '运维人员', '创建时间']]
    closed_df = df[df['工单状态'].isin(['Resolved', 'Closed'])][['项目名称', '工单主题', '工单单号', '运维人员', '创建时间']]

    # 生成 HTML
    html = f"""
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<title>{year}年第{week_num}周运维工作汇报</title>
<style>
body{{font-family:Arial,Helvetica,sans-serif;margin:0;padding:20px;background:#fafafa;color:#333}}
.card{{background:#fff;border-radius:8px;box-shadow:0 2px 8px rgba(0,0,0,.1);padding:15px;margin-bottom:20px}}
.card h3{{margin:0 0 10px;font-size:18px;border-bottom:1px solid #eee;padding-bottom:5px}}
.summary{{display:flex;justify-content:space-around;text-align:center}}
.summary div{{flex:1}}
.summary .num{{font-size:28px;font-weight:bold;color:#2575fc}}
table{{width:100%;border-collapse:collapse;margin-top:10px}}
th,td{{border:1px solid #ddd;padding:8px;text-align:center}}
th{{background:#f2f2f2}}
</style>
</head>
<body>
<div class="card">
  <h3>{year}年第{week_num}周运维关键指标总览</h3>
  <div class="summary">
    <div><div class="num">{total_tickets}</div><div>总工单</div></div>
    <div><div class="num">{resolved_tickets}</div><div>已解决</div></div>
    <div><div class="num">{open_tickets}</div><div>未完成</div></div>
    <div><div class="num">{total_hours}</div><div>总工时(h)</div></div>
  </div>
</div>

<div class="card">
   <h3>运维人员工作量</h3>
   <table>
     <thead><tr><th>运维人员</th><th>工单数量</th><th>总工时(h)</th></tr></thead>
     <tbody>
       {"".join([f"<tr><td>{r['运维人员']}</td><td>{r['工单数量']}</td><td>{round(r['总工时'],2)}</td></tr>" for _, r in staff_stats.iterrows()])}
     </tbody>
   </table>
 </div>

<div class="card">
  <h3>运维人员工作量</h3>
  <table>
    <thead>
      <tr>
        <th>运维人员</th>
        <th>工单数量</th>
        <th>总工时(h)</th>
        <th>加班工时</th>
      </tr>
    </thead>
    <tbody>
      {"".join([
          f"<tr>"
          f"<td>{r['运维人员']}</td>"
          f"<td>{r['工单数量']}</td>"
          f"<td>{round(r['总工时'],2)}</td>"
          f"<td>{'无加班' if r['总工时'] <= 37.5 else round(r['总工时'] - 37.5, 2)}</td>"
          f"</tr>"
          for _, r in staff_stats.iterrows()
      ])}
    </tbody>
  </table>
</div>

<div class="card">
  <h3>项目 TOP3</h3>
  <table>
    <thead><tr><th>项目名称</th><th>工单数量</th></tr></thead>
    <tbody>
      {"".join([f"<tr><td>{r['项目名称']}</td><td>{r['工单数量']}</td></tr>" for _, r in project_stats.iterrows()])}
    </tbody>
  </table>
</div>

<div class="card">
  <h3>任务类型分布</h3>
  <table>
    <thead><tr><th>任务类型</th><th>数量</th></tr></thead>
    <tbody>
      {"".join([f"<tr><td>{r['任务类型']}</td><td>{r['数量']}</td></tr>" for _, r in task_type_stats.iterrows()])}
    </tbody>
  </table>
</div>

<div class="card">
  <h3>未完成工单明细</h3>
  <table>
    <thead><tr><th>项目名称</th><th>工单主题</th><th>工单单号</th><th>运维人员</th><th>创建时间</th></tr></thead>
    <tbody>
      {"".join([f"<tr><td>{r['项目名称']}</td><td>{r['工单主题']}</td><td>{r['工单单号']}</td><td>{r['运维人员']}</td><td>{r['创建时间']}</td></tr>" for _, r in open_df.iterrows()])}
    </tbody>
  </table>
</div>

<div class="card">
  <h3>已完成工单明细</h3>
  <table>
    <thead><tr><th>项目名称</th><th>工单主题</th><th>工单单号</th><th>运维人员</th><th>创建时间</th></tr></thead>
    <tbody>
      {"".join([f"<tr><td>{r['项目名称']}</td><td>{r['工单主题']}</td><td>{r['工单单号']}</td><td>{r['运维人员']}</td><td>{r['创建时间']}</td></tr>" for _, r in closed_df.iterrows()])}
    </tbody>
  </table>
</div>

</body>
</html>
"""

    # 输出
    out = output_file or input_file.replace('.xlsx', '_static.html')
    with open(out, 'w', encoding='utf-8') as f:
        f.write(html)
    print(f"静态看板已生成：{out}")

if __name__ == "__main__":
    # 固定输入文件
    input_path = r"D:\check\issues.xlsx"
    output_path = input_path.replace('.xlsx', '_static.html')
    generate_dashboard(input_path, output_path)

